{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "intro",
   "metadata": {},
   "source": [
    "# 为什么要进行数据清洗\n",
    "\n",
    "## 核心原因\n",
    "\n",
    "**\"Garbage In, Garbage Out\"** - 脏数据导致错误结论\n",
    "\n",
    "- **时间占比**：数据科学家60%-80%的时间用于数据清洗\n",
    "- **质量影响**：脏数据直接影响分析结果和商业决策\n",
    "- **成本代价**：数据质量问题可能导致重大经济损失\n",
    "\n",
    "---\n",
    "\n",
    "## 数据为什么会\"脏\"？\n",
    "\n",
    "| 来源 | 具体问题 | 示例 |\n",
    "|------|----------|------|\n",
    "| **人为因素** | 录入错误、格式不一致 | 电话号码输错、姓名多空格、日期格式混乱 |\n",
    "| **系统因素** | 多系统整合、编码不同 | SAP与CRM系统数据格式不一致 |\n",
    "| **环境因素** | 传感器故障、网络中断 | 温度异常、数据丢失、记录中断 |\n",
    "\n",
    "---\n",
    "\n",
    "## 脏数据的代价\n",
    "\n",
    "### 真实案例\n",
    "\n",
    "1. **零售业**：重复记录 → 错误库存预测 → 缺货/积压\n",
    "2. **金融业**：异常值未处理 → 风险评估偏差 → 错误贷款决策\n",
    "3. **制造业**：传感器数据噪声 → 维护系统失效 → 生产线停产\n",
    "\n",
    "---\n",
    "\n",
    "## 常见数据质量问题\n",
    "\n",
    "| 问题类型 | 影响 | 检测方法 |\n",
    "|----------|------|----------|\n",
    "| **缺失值** | 影响统计结果 | `isnull().sum()` |\n",
    "| **异常值** | 导致模型偏差 | 箱线图、IQR |\n",
    "| **重复值** | 数据失真 | `duplicated().sum()` |\n",
    "| **格式不一致** | 无法合并分析 | 正则表达式检查 |\n",
    "| **单位不统一** | 计算错误 | 标准化检查 |\n",
    "| **数据类型错误** | 程序报错 | `dtypes` 检查 |\n",
    "\n",
    "---\n",
    "\n",
    "## 数据清洗的技能要求\n",
    "\n",
    "```\n",
    "数据清洗 = 专业知识 + 统计思维 + 工程能力\n",
    "    │           │            │           │\n",
    "    │           │            │           └─ 高效处理大规模数据\n",
    "    │           │            └─ 识别异常值、处理缺失值\n",
    "    │           └─ 理解业务规则和行业特点\n",
    "    └─ 艺术与科学的结合\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "## 未来趋势\n",
    "\n",
    "- **自动化清洗**：机器学习算法自动检测和修正数据问题\n",
    "- **实时清洗**：流式数据处理,在线交易/物联网场景\n",
    "- **协同清洗**：多部门共同维护数据质量标准\n",
    "\n",
    "---\n",
    "\n",
    "## 实践：创建脏数据集\n",
    "\n",
    "下面的代码将创建一个包含各种数据质量问题的数据集,用于后续清洗练习。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "create-messy-data",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "import random\n",
    "import os\n",
    "\n",
    "def create_messy_data():\n",
    "    \"\"\"创建包含各种数据问题的综合数据集\"\"\"\n",
    "    \n",
    "    # 设置随机种子,确保可重复性\n",
    "    np.random.seed(42)\n",
    "    n_rows = 1000\n",
    "    \n",
    "    # ============== 1. 基础信息数据 ==============\n",
    "    # 1.1 日期数据（格式不统一）\n",
    "    dates = [(datetime(2023, 1, 1) + timedelta(days=x)).strftime('%Y-%m-%d') for x in range(n_rows)]\n",
    "    dates[10:20] = ['2023/01/15', '01-15-2023', '2023.01.16', '20230117', 'invalid_date',\n",
    "                    '2023-13-01', '2023-01-32', '01/19/2023', '2023-01', '2023']\n",
    "\n",
    "    # 1.2 姓名（包含格式问题）\n",
    "    names = ['张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十']\n",
    "    full_names = [random.choice(names) for _ in range(n_rows)]\n",
    "    full_names[30:40] = [' 张三', '李四 ', ' 王五 ', 'zhang san', 'li si', '王Wu', \n",
    "                         'ZHANG SAN', '李   四', '王\\t五', '赵\\n六']\n",
    "\n",
    "    # 1.3 年龄（包含异常值）\n",
    "    ages = list(np.random.randint(20, 60, n_rows))\n",
    "    ages[40:50] = [-1, 0, 150, 200, None, None, 999, 25, 30, 'invalid_age']\n",
    "\n",
    "    # 1.4 工资（包含格式问题）\n",
    "    salaries = list(np.random.randint(5000, 20000, n_rows))\n",
    "    salaries[50:60] = [-5000, '1,000,000', '5,000', 'N/A', None, None,\n",
    "                       '8888.88', '12345.678', 'invalid_salary', '10000+']\n",
    "\n",
    "    # ============== 2. 联系方式数据 ==============\n",
    "    # 2.1 邮箱（包含无效格式）\n",
    "    emails = [f'user{i}@example.com' for i in range(n_rows)]\n",
    "    emails[60:70] = ['invalid_email', 'user@.com', '@example.com', 'user@', \n",
    "                     'user@example..com', 'user@com', ' user@example.com ',\n",
    "                     'user@example.com.', '.user@example.com', 'user.@example.com']\n",
    "\n",
    "    # 2.2 手机号（包含格式问题）\n",
    "    phones = [f'1{random.choice([\"3\", \"5\", \"7\", \"8\", \"9\"])}{random.randint(100000000, 999999999)}' \n",
    "              for _ in range(n_rows)]\n",
    "    phones[70:80] = ['123', '12345678901234', '2345678901', 'abc12345678',\n",
    "                     '13-888-88888', '138 8888 8888', None, None, \n",
    "                     '+86-13888888888', '086-13888888888']\n",
    "\n",
    "    # 2.3 地址（包含格式问题）\n",
    "    addresses = [f'北京市朝阳区建国路{random.randint(1, 100)}号' for _ in range(n_rows)]\n",
    "    addresses[80:90] = [' 北京市朝阳区建国路1号 ', '北京市 朝阳区 建国路2号',\n",
    "                        'beijing chaoyang', None, None, '', \n",
    "                        '北京市朝阳区建国路1号\\n', '\\t北京市朝阳区建国路2号',\n",
    "                        '   北京市朝阳区建国路3号   ', '北京市朝阳区建国路1号']\n",
    "\n",
    "    # ============== 3. 组织数据 ==============\n",
    "    # 3.1 部门（名称不统一）\n",
    "    departments = list(np.random.choice(['销售部', '技术部', '市场部', '人事部'], n_rows))\n",
    "    departments[90:100] = ['Sales', 'Tech', 'Marketing', 'HR', \n",
    "                          '销售部门', '技术部门', '市场部门', '人事部门',\n",
    "                          'sales', 'SALES']\n",
    "\n",
    "    # 3.2 产品编码（格式不统一）\n",
    "    product_codes = [f'PRD{random.randint(1000, 9999)}' for _ in range(n_rows)]\n",
    "    product_codes[100:110] = ['prd1234', 'PRD-1234', 'PRD_1234', 'prd_1234',\n",
    "                             'Prd1234', 'PRD1234 ', ' PRD1234', None, None, '']\n",
    "\n",
    "    # ============== 4. 统计数据 ==============\n",
    "    # 4.1 订单金额（需要统计经验判断）\n",
    "    order_amounts = list(np.random.lognormal(mean=8, sigma=0.6, size=n_rows))\n",
    "    order_amounts = [round(x, 2) for x in order_amounts]\n",
    "    order_amounts[200:210] = [0.01, 1000000, -50, 500.999, 'pending', \n",
    "                              '1,234.56', '待审核', None, float('inf'), 'error']\n",
    "\n",
    "    # 4.2 客户评分（需要业务经验判断）\n",
    "    customer_scores = list(np.random.normal(loc=85, scale=10, size=n_rows))\n",
    "    customer_scores = [min(100, max(0, round(x, 1))) for x in customer_scores]\n",
    "    customer_scores[210:220] = [150, -10, 'A+', '100分', 'Excellent', \n",
    "                                '待评估', None, '未评分', '95~100', 'N/A']\n",
    "\n",
    "    # 4.3 物流时效（需要统计经验判断）\n",
    "    delivery_times = list(np.random.gamma(shape=2, scale=10, size=n_rows))\n",
    "    delivery_times = [round(x, 1) for x in delivery_times]\n",
    "    delivery_times[220:230] = [0.1, 240, -2, '2-3天', '已签收', \n",
    "                               None, 'delayed', '48h+', '待确认', 0]\n",
    "\n",
    "    # 4.4 评价数量（需要文本分析经验）\n",
    "    review_counts = list(np.random.poisson(lam=100, size=n_rows))\n",
    "    review_counts[230:240] = [-1, 999999, 'hidden', '1000+', None, \n",
    "                              '待统计', 'restricted', '有争议', float('nan'), '未知']\n",
    "\n",
    "    # ============== 创建DataFrame ==============\n",
    "    df = pd.DataFrame({\n",
    "        '日期': dates,\n",
    "        '姓名': full_names,\n",
    "        '年龄': ages,\n",
    "        '工资': salaries,\n",
    "        '邮箱': emails,\n",
    "        '手机号': phones,\n",
    "        '地址': addresses,\n",
    "        '部门': departments,\n",
    "        '产品编码': product_codes,\n",
    "        '订单金额': order_amounts,\n",
    "        '客户评分': customer_scores,\n",
    "        '物流时效': delivery_times,\n",
    "        '评价数量': review_counts\n",
    "    })\n",
    "\n",
    "    # 随机打乱一些行的顺序\n",
    "    df = df.sample(frac=1).reset_index(drop=True)\n",
    "\n",
    "    # 添加一些重复行\n",
    "    df = pd.concat([df, df.iloc[0:20]], ignore_index=True)\n",
    "\n",
    "    # 确保输出目录存在（使用正确的相对路径）\n",
    "    if not os.path.exists('../data'):\n",
    "        os.makedirs('../data')\n",
    "\n",
    "    # 保存到CSV文件\n",
    "    df.to_csv('../data/messy_data.csv', index=False, encoding='utf-8')\n",
    "    \n",
    "    return df\n",
    "\n",
    "# 创建脏数据集\n",
    "df = create_messy_data()\n",
    "\n",
    "print(\"=== 数据集中包含的问题类型 ===\")\n",
    "print(\"\\n1. 基础数据问题：日期/姓名/年龄/工资格式不统一\")\n",
    "print(\"2. 联系方式问题：邮箱/手机号/地址格式错误\")\n",
    "print(\"3. 组织数据问题：部门/产品编码命名不一致\")\n",
    "print(\"4. 统计异常问题：订单金额/评分/时效/评价数据异常\")\n",
    "print(\"5. 其他问题：重复数据、特殊值、系统错误\\n\")\n",
    "\n",
    "print(\"=== 数据预览 ===\")\n",
    "print(df.head())\n",
    "print(\"\\n=== 数据信息 ===\")\n",
    "print(df.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "check-problems",
   "metadata": {},
   "source": [
    "## 快速检查数据质量\n",
    "\n",
    "### 常用检查方法\n",
    "\n",
    "| 检查项 | 代码 | 说明 |\n",
    "|--------|------|------|\n",
    "| 缺失值 | `df.isnull().sum()` | 统计每列缺失值数量 |\n",
    "| 重复值 | `df.duplicated().sum()` | 统计重复行数量 |\n",
    "| 数据类型 | `df.dtypes` | 检查列的数据类型 |\n",
    "| 唯一值 | `df['col'].nunique()` | 检查列的唯一值数量 |\n",
    "| 值分布 | `df['col'].value_counts()` | 查看值的分布情况 |\n",
    "| 统计信息 | `df.describe()` | 数值列的统计摘要 |\n",
    "\n",
    "---\n",
    "\n",
    "## 小结\n",
    "\n",
    "**核心观点**：\n",
    "1. 数据清洗是数据分析的基础,占据60%-80%的工作时间\n",
    "2. 脏数据来自人为、系统、环境三大因素\n",
    "3. 数据质量问题可能导致严重的商业损失\n",
    "4. 数据清洗需要专业知识、统计思维和工程能力\n",
    "\n",
    "**下一步**：学习具体的数据清洗技术和方法"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
